package com.deryun.tyl.bean.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.deryun.tyl.bean.ContentMessage;
import com.deryun.tyl.bean.tempBean.FriendConnect;
import com.deryun.tyl.bean.tempBean.MyPublish;

public interface ContentMessageMapper {

	@Select({
		"SELECT * FROM `content_message` WHERE add_user_id = #{0,jdbcType=VARCHAR} ORDER BY id DESC"
	})
	@ResultMap("BaseResultMap")
	List<ContentMessage> getAll(String userId);
	
	@Select({
		"SELECT * FROM `content_message` ORDER BY id DESC"
	})
	@ResultMap("BaseResultMap")
	List<ContentMessage> getAdminAll();
	
	@Select({
		"SELECT "
		+ "(select count(*) from content_message where type = 1 and add_user_id = #{0,jdbcType=VARCHAR}) as 'content',"
		+ "(select count(*) from content_message where type = 2 and add_user_id = #{0,jdbcType=VARCHAR}) as 'product',"
		+ "(select count(*) from content_message where type = 3 and add_user_id = #{0,jdbcType=VARCHAR}) as 'project', "
		+ "(select count(*) from content_message where type = 4 and add_user_id = #{0,jdbcType=VARCHAR}) as 'activity'"
	})
	MyPublish getCount(String userId);
	
	@Select({
		"SELECT "
		+ "(select count(*) from content_message where type = 1) as 'content',"
		+ "(select count(*) from content_message where type = 2) as 'product',"
		+ "(select count(*) from content_message where type = 3) as 'project', "
		+ "(select count(*) from content_message where type = 4) as 'activity'"
	})
	MyPublish getAdminCount();
	
	@Select({
		"SELECT "
		+ "(select count(*) from content_message where type = 1 and is_pass = 0) as 'content',"
		+ "(select count(*) from content_message where type = 2 and is_pass = 0) as 'product',"
		+ "(select count(*) from content_message where type = 3 and is_pass = 0) as 'project', "
		+ "(select count(*) from content_message where type = 4 and is_pass = 0) as 'activity'"
	})
	MyPublish getAdminAuthCount();
	
	@Select({
		"SELECT * FROM content_message WHERE is_pass = '0'"
	})
	@ResultMap("BaseResultMap")
	List<ContentMessage> getAdminAuthCM();
	
	@Insert({
		"INSERT INTO content_message ("
		+ "add_user_id,add_user_name,title,plate,grade,content,is_pass,create_time,type,"
		+ "supplier,product_name,product_price,heading,product_category,region,contacts,"
		+ "contact_information,procurement_mode,project_category,one_key_pic) "
		+ "VALUES ("
		+ "#{addUserId,jdbcType=VARCHAR},"
		+ "#{addUserName,jdbcType=VARCHAR},"
		+ "#{title,jdbcType=VARCHAR},"
		+ "#{plate,jdbcType=VARCHAR},"
		+ "#{grade,jdbcType=VARCHAR},"
		+ "#{content,jdbcType=VARCHAR},"
		+ "#{isPass,jdbcType=VARCHAR},"
		+ "#{createTime,jdbcType=VARCHAR},"
		+ "#{type,jdbcType=VARCHAR},"
		+ "#{supplier,jdbcType=VARCHAR},"
		+ "#{productName,jdbcType=VARCHAR},"
		+ "#{productPrice,jdbcType=VARCHAR},"
		+ "#{heading,jdbcType=VARCHAR},"
		+ "#{productCategory,jdbcType=VARCHAR},"
		+ "#{region,jdbcType=VARCHAR},"
		+ "#{contacts,jdbcType=VARCHAR},"
		+ "#{contactInformation,jdbcType=VARCHAR},"
		+ "#{procurementMode,jdbcType=VARCHAR},"
		+ "#{projectCategory,jdbcType=VARCHAR},"
		+ "#{oneKeyPic,jdbcType=VARCHAR}"
		+ ")"
	})
	int addCM(ContentMessage message);
	
	@Select({
		"SELECT * FROM content_message WHERE id = #{0,jdbcType=INTEGER}"
	})
	@ResultMap("BaseResultMap")
	ContentMessage getCM(int id);
	
	@Delete({
		"DELETE FROM content_message WHERE id = #{0,jdbcType=INTEGER}"
	})
	int deleteCM(int id);
	
	@Delete({
		"DELETE FROM content_message WHERE plate = '一键通介绍'"
	})
	int delCMInfo();
	
	@Update({
		"UPDATE content_message SET "
		+ "title = #{title,jdbcType=VARCHAR},"
		+ "plate = #{plate,jdbcType=VARCHAR},"
		+ "grade = #{grade,jdbcType=VARCHAR},"
		+ "content = #{content,jdbcType=VARCHAR},"
		+ "is_pass = '0',"
		+ "product_price = #{productPrice,jdbcType=VARCHAR},"
		+ "heading = #{heading,jdbcType=VARCHAR},"
		+ "product_category = #{productCategory,jdbcType=VARCHAR},"
		+ "region = #{region,jdbcType=VARCHAR},"
		+ "contacts = #{contacts,jdbcType=VARCHAR},"
		+ "contact_information = #{contactInformation,jdbcType=VARCHAR},"
		+ "procurement_mode = #{procurementMode,jdbcType=VARCHAR} "
		+ "WHERE "
		+ "id = #{id,jdbcType=INTEGER}"
	})
	int modifyCM(ContentMessage message);
	
	@Update({
		"UPDATE content_message SET is_pass = 1 WHERE id = #{0,jdbcType=INTEGER}"
	})
	int accessCM(int id);
	
	@Update({
		"UPDATE content_message SET is_pass = 2 WHERE id = #{0,jdbcType=INTEGER}"
	})
	int refuseCM(int id);
	
	List<ContentMessage> searchCM(String plate,String title);
	
	@Select({
		"(SELECT * FROM content_message c WHERE c.grade = '区县级' and is_pass = '1' and plate = '政策法规' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '省市级' and is_pass = '1' and plate = '政策法规' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '国家级' and is_pass = '1' and plate = '政策法规' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '街道级' and is_pass = '1' and plate = '政策法规' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '省市级' and is_pass = '1' and plate = '标准规范' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '区县级' and is_pass = '1' and plate = '标准规范' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '街道级' and is_pass = '1' and plate = '标准规范' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '省市级' and is_pass = '1' and plate = '采购公告' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '区县级' and is_pass = '1' and plate = '采购公告' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '街道级' and is_pass = '1' and plate = '采购公告' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '省市级' and is_pass = '1' and plate = '采购结果公告' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '区县级' and is_pass = '1' and plate = '采购结果公告' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE c.grade = '街道级' and is_pass = '1' and plate = '采购结果公告' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE is_pass = '1' and plate = '一键通快讯' ORDER BY c.create_time DESC LIMIT 0,6) "
	})
	@ResultMap("BaseResultMap")
	List<ContentMessage> getSixCM();
	
	@Select({
		"(SELECT * FROM content_message c WHERE is_pass = '1' and plate = '一键通快讯' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE is_pass = '1' and plate = '一键通产品' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE is_pass = '1' and plate = '一键通活动' ORDER BY c.create_time DESC LIMIT 0,6) "
	})
	@ResultMap("BaseResultMap")
	List<ContentMessage> getSixKX();
	
	@Select({
		"SELECT * FROM content_message WHERE plate = #{0,jdbcType=VARCHAR}"
	})
	@ResultMap("BaseResultMap")
	List<ContentMessage> getCMByPlate(String plate);
	
	@Select({
		"(SELECT DISTINCT heading, product_category FROM content_message WHERE type = 2 AND heading = '货物类' LIMIT 0,3) "
		+ "UNION ALL "
		+ "(SELECT DISTINCT heading,product_category FROM content_message WHERE type = 2 AND heading = '服务类' LIMIT 0,3) "
		+ "UNION ALL "
		+ "(SELECT DISTINCT heading,product_category FROM content_message WHERE type = 2 AND heading = '工程类' LIMIT 0,3)"
	})
	@ResultMap("BaseResultMap")
	List<ContentMessage> getProductType();
	
	@Select({
		"SELECT * FROM friend_connect"
	})
	List<FriendConnect> getFC();
	
	@Update({
		"UPDATE friend_connect SET title = #{0,jdbcType=VARCHAR},address = #{1,jdbcType=VARCHAR} WHERE position = #{2,jdbcType=VARCHAR}"
	})
	int modifyFC(String title,String address,String position);
	
	@Select({
		"SELECT * FROM content_message WHERE plate = '一键通介绍' "
	})
	@ResultMap("BaseResultMap")
	ContentMessage getOnekeyInfo();
	
	List<ContentMessage> searchVCM(String startTime,String endtime,String grade,String plate,String title);
	
	@Select({
		"(SELECT * FROM content_message c WHERE is_pass = '1' and plate = '融资动态' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE is_pass = '1' and plate = '采购结果公告' ORDER BY c.create_time DESC LIMIT 0,6) "
		+ "union ALL "
		+ "(SELECT * FROM content_message c WHERE is_pass = '1' and plate = '融资产品' ORDER BY c.create_time DESC LIMIT 0,8) "
	})
	@ResultMap("BaseResultMap")
	List<ContentMessage> getSixRZ();
	
	List<ContentMessage> searchZFCG(ContentMessage message);
	
	
}
